<?php
/* WARNING: this class needs SQLi fixes with mysqli_real_escape_string */
class database{
	// private vars with config vals also
	private $db_host = CONFIG_DB_HOST;
	private $db_name = CONFIG_DB_NAME;
	private $db_user = CONFIG_DB_USER;
	private $db_password = CONFIG_DB_PASS;
	
	// identifies the link to the rdbms
	private $link;
	
	// stores the site id of this site
	private $siteId;
	
	// defines the language code
	private $languageCode;
	
	/* these values are used to count the number 
	 * of calls to procedures, queries to views 
	 * and thereby also the number of total database 
	 * queries which then is used to analyze the usage
	 * of the database and output it for reporting */
	public $procedureCallCount = 0;
	public $viewQueryCount = 0;
	
	// constructor connecting the database
	function __construct(){
		// try to connect to the db server
		$this->link = mysqli_connect($this->db_host, $this->db_user, 
								$this->db_password, $this->db_name);
		
		// check whether the connection was successfull or not
		if($this->link == false){
			// throw an exception when the connection failed
			throw new Exception("Unable to connect to database");
		}else{
			// set the client charset to utf-8
			mysqli_set_charset($this->link,"utf8");			
		}
	}
	
	// returns the site id to use with the queries
	private function getSiteId(){
		return $this->siteId;
	}
	
	/* this method queries all available languages 
	 * for the current site and returns them */
	public function getLanguageList(){
		$result = array();
		$sql = "SELECT language_code, language_name "
			."FROM ".$this->db_name.".site_list WHERE site_id = "
			.mysqli_real_escape_string($this->link,$this->getSiteId());
		$lang_result = mysqli_query($this->link,$sql);
		$l = 0;
		while($row = mysqli_fetch_assoc($lang_result)){
			$result[$l] = array();
			$result[$l]["code"] = $row["language_code"];
			$result[$l]["name"] = $row["language_name"];
			$l++;
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($lang_result);		
		return $result;
	}
	
	/* sets the id of this site to use with the database */
	public function setSite($siteid){
		$this->siteId = $siteid;
	}
	
	/* sets the language code the site currently uses */
	public function setLanguageCode($code){
		$this->languageCode = $code;
	}
	
	// returns the current language code in use
	public function getLanguageCode(){
		return $this->languageCode;
	}
	
	/* returns the site id for the domain provided or -1 if 
	 * the domain has no site in the database. */
	public function getSiteIdByDomain($domain){
		$result = -1;
		$sql = "SELECT site_id FROM ".$this->db_name.".setting_list "
				."WHERE ".$this->db_name.".setting_list.setting_value LIKE '%"
				.mysqli_real_escape_string($this->link,$domain)."'";
		$site_result = mysqli_query($this->link,$sql);
		while($row = mysqli_fetch_assoc($site_result)){
			$result = $row["site_id"];
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($site_result);		
		return $result;
	}
	
	/* returns the default domain configured in the system which 
	 * can be used as a fall back when a client uses a non-configured 
	 * domain with the system (uses CDN_PRIMARY_DOMAIN setting) */
	public function getDefaultDomain(){
		$result = "";
		$sql = "SELECT l.setting_value AS default_domain "
			 ."FROM ".$this->db_name.".site AS s, "
			 .$this->db_name."setting_list AS l "
			 ."WHERE l.setting_code = 'CDN_PRIMARY_DOMAIN' "
			 ."AND l.site_id = s.site_id "
			 ."AND s.site_is_default = 1 "
			 ."LIMIT 0,1";
			 
		$site_result = mysqli_query($this->link,$sql);
		if($site_result!=null){
			while($row = mysqli_fetch_assoc($site_result)){
				$result = $row["default_domain"];
			}
		}
		
		// increment the counter
		$this->viewQueryCount++;

		// if the site is not properly configured
		// it would show a warning message here
		if($site_result!=null){
			mysqli_free_result($site_result);
		}
		return $result;
	}
	
	/* returns all settings and their values for this site */
	public function getSettingList(){
		// first read all available options
		$result = $this->getSettingOptionList();
		
		// query the options set in the database
		// the call to "getSettingOptionList" above
		// is done to return all possible settings
		// including those that are not set
		$sql = "SELECT setting_code, setting_name, setting_value FROM "
				.$this->db_name.".setting_list WHERE site_id = ".$this->getSiteId();
		$setting_result = mysqli_query($this->link,$sql);
		while($row = mysqli_fetch_assoc($setting_result)){
			for($s=0; $s<count($result);$s++){
				if($result[$s]["code"]==$row["setting_code"]){
					$result[$s]["value"] = $row["setting_value"];
				}
			}
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($setting_result);
		return $result;				
	}
	
	/* returns all available setting options from the database */
	private function getSettingOptionList(){
		$result = array();
		$sql = "SELECT setting_code, setting_name FROM "
				.$this->db_name.".setting ORDER BY setting_code ASC";
		$setting_result = mysqli_query($this->link,$sql);
		$i=0;
		while($row = mysqli_fetch_assoc($setting_result)){
			$result[$i] = array();
			$result[$i]["code"] = $row["setting_code"];
			$result[$i]["name"] = $row["setting_name"];
			$result[$i]["value"] = ""; // empty as default
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($setting_result);
		return $result;					
	}
	
	/* stores a new setting in the database */
	public function setSetting($settingCode, $settingValue){
		// prepare the sql statement
		$sql = "CALL ".$this->db_name.".set_setting ('"
			.mysqli_real_escape_string($this->link,$settingCode)."','"
			.mysqli_real_escape_string($this->link,$settingValue)."',"
			.$this->getSiteId().")";
		$setting_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($setting_result)){
			mysqli_free_result($setting_result);
		}	
		
		// increment the counter
		$this->procedureCallCount++;		
	}
	
	/* searches an article in the database */
	public function searchArticle($query){
		$result = array();
		$sql = "CALL ".$this->db_name.".search_article ('"
				.mysqli_real_escape_string($this->link,$query)."',"
				.$this->getSiteId().")";

		$search_result = mysqli_query($this->link,$sql);
		$i = 0;
		while($row = mysqli_fetch_assoc($search_result)){
			$result[$i] = array();
			$result[$i]["title"] = $row["title"];
			$result[$i]["url"] = $row["url"];
			$result[$i]["body"] = strip_tags($row["body"]);
			$result[$i]["index"] = $row["total_index_value"];
			$i++;
		}
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($search_result)){
			mysqli_free_result($search_result);
		}
		
		// increment the counter
		$this->procedureCallCount++;
		
		return $result;			
	}
	
	/* deletes an article from the database*/
	public function deleteComment($id){
		// prepare the sql statement for the deletion
		$sql = "CALL ".$this->db_name.".delete_comment (".$id.")";
		$delete_comment_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($delete_comment_result)){
			mysqli_free_result($delete_comment_result);
		}	
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* adds a comment to the database */
	public function addComment($username,$article,$text){
		/* Security Information:
		 * as anyone with a valid facebook account is allowed
		 * to insert data into the database here we have to be
		 * very strict with the type of string data that is
		 * allowed to be inserted. We need to make sure that we
		 * properly prevent cross-site-scripting and code injection
		 * here. Removing the html and script stuff should solve
		 * that security problem.
		 * */
		$secured_text = strip_tags($text);
		
		/* limit the size of the comment to 5000 chars */
		if(strlen($secured_text)>5000){
			$secured_text = substr($secured_text,0,5000);
		}
		
		// create the sql command for the insert
		$sql = "CALL ".$this->db_name.".add_comment ('"
				.mysqli_real_escape_string($this->link,$username)."','"
				.mysqli_real_escape_string($this->link,$article)."','"
				.mysqli_real_escape_string($this->link,$secured_text)."','"
				.date("Y-m-d H:i")."',".$this->getSiteId().")";

		$addcomment_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($addcomment_result)){
			mysqli_free_result($addcomment_result);
		}	
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* gets all comments for an article */
	public function getArticleCommentList($article){
		$result = array();
		$sql = "SELECT * FROM ".$this->db_name.".comment_list "
				."WHERE ".$this->db_name.".comment_list.article = '"
				.mysqli_real_escape_string($this->link,$article)."' "
				."AND ".$this->db_name.".comment_list.site_id = "
				.$this->getSiteId();
		$comment_result = mysqli_query($this->link,$sql);
		$i = 0;
		while($row = mysqli_fetch_assoc($comment_result)){
			$result[$i] = $row;
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($comment_result);
		return $result;		
	}
	
	/* deletes an article */
	public function deleteArticle($url){
		// build sql for sp execution 
		$sql = "CALL ".$this->db_name.".delete_article ('"
			.mysqli_real_escape_string($this->link,$url)."')";

		// fire the sp on the existing link
		$delete_article_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($delete_article_result)){
			mysqli_free_result($delete_article_result);
		}	

		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* adds a new article or updates and existing one */
	public function addArticle($title, $url, $keyword, $body, $author, $published, $date, $image){
		$sql_published = "0";
		if($published){$sql_published="1";}
		
		// build sql for sp execution 
		$sql = "CALL ".$this->db_name.".add_article "
				."('".mysqli_real_escape_string($this->link,$title)."','"
				.mysqli_real_escape_string($this->link,$url)."', '"
				.mysqli_real_escape_string($this->link,$keyword)."', '"
				.mysqli_real_escape_string($this->link,$body)."','"
				.$author."', ".$sql_published.",'".date("Y-m-d H:i:s",$date)."',"
				."'".mysqli_real_escape_string($this->link,$image)."',"
				.$this->getSiteId().",'".$this->getLanguageCode()."')";

		// fire the sp on the existing link
		$add_article_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}if(!is_bool($add_article_result)){
			mysqli_free_result($add_article_result);			
		}
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* returns a single article */
	public function getArticle($url){
		$result = array();
		$sql = "SELECT title, url, published, date, body, image, "
				."author_firstname, author_lastname, keyword "
				."FROM article_list WHERE url = '"
				.mysqli_real_escape_string($this->link,$url)."' "
				."AND site_id = ".$this->getSiteId()." "
				."AND language_code = '".$this->getLanguageCode()."'";
		$article_result = mysqli_query($this->link,$sql);
		$i = 0;
		while($row = mysqli_fetch_assoc($article_result)){
			$result = $row;
			// there is no timestamp when it comes out of the db
			// as the date is represented by a string and therefore
			// we do parse the proper timestamp here.
			$result["date_ts"] = strtotime($result["date"]);
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($article_result);
		return $result;		
	}
	
	/* returns a list of all articles in the database
	 * without the content in the articles */
	public function getArticleList(){
		$result = array();
		$sql = "SELECT title, url, published, date, image, short, "
				."author_firstname, author_lastname, keyword "
				."FROM article_list WHERE site_id = ".$this->getSiteId()
				." AND language_code = '".$this->getLanguageCode()."'";

		$article_result = mysqli_query($this->link,$sql);
		$i = 0;
		while($row = mysqli_fetch_assoc($article_result)){
			$result[$i] = $row;
			// there is no timestamp when it comes out of the db
			// as the date is represented by a string and therefore
			// we do parse the proper timestamp here.
			$result[$i]["date_ts"] = strtotime($result[$i]["date"]);
			$result[$i]["short"] = substr(strip_tags(stripslashes($result[$i]["short"])),0,300);
			
			// change the feature image of this article
			if(strlen($result[$i]["image"])>0){
				// add the feature images with different sizes
				$result[$i]["smallimage"] = substr($result[$i]["image"], 0, strrpos($result[$i]["image"],"."))
									."_100".substr($result[$i]["image"], strrpos($result[$i]["image"],"."));
				$result[$i]["image"] = substr($result[$i]["image"], 0, strrpos($result[$i]["image"],"."))
									."_200".substr($result[$i]["image"], strrpos($result[$i]["image"],"."));
			}
			
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($article_result);
		return $result;
	}
	
	/* Gets a specific category */
	public function getCategory($url){
		$result = array();
		$sql = "SELECT * FROM ".$this->db_name.".category_published_list "
				."WHERE url = '".mysqli_real_escape_string($this->link,$url)."' "
				."AND site_id = ".mysqli_real_escape_string($this->link,$this->getSiteId())
				." AND language_code = '".mysqli_real_escape_string
						($this->link,$this->getLanguageCode())."'";
		$category_result = mysqli_query($this->link,$sql);

		$i = 0;
		while ($row = mysqli_fetch_assoc($category_result)) {
			$result[$i] = array();
			$result[$i]["name"] = $row["name"];
			$result[$i]["url"] = $row["url"];
			$result[$i]["body"] = $row["body"];
			$result[$i]["article_count"] = $row["article_count"];
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;
		
		mysqli_free_result($category_result);
		return $result;		
	}

	/* Compared to <getCategoryList> this function 
	 * calls a different procedure which only returns 
	 * the categories that do have active articles. */
	public function getCategoryPublishedList(){
		$result = array();
		$sql = "SELECT id, name, url, article_count FROM "
				.$this->db_name.".category_published_list "
				."WHERE site_id = ".mysqli_real_escape_string($this->link,$this->getSiteId())
				." AND language_code = '".mysqli_real_escape_string
						($this->link,$this->getLanguageCode())."'"
				." ORDER BY ".$this->db_name.".category_published_list.article_count DESC;";
		$category_result = mysqli_query($this->link,$sql);

		$i = 0;
		while ($row = mysqli_fetch_assoc($category_result)) {
			$result[$i] = array();
			$result[$i]["name"] = $row["name"];
			$result[$i]["url"] = $row["url"];
			$result[$i]["article_count"] = $row["article_count"];
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;
		
		mysqli_free_result($category_result);
		return $result;
	}
	
	/* returns the list of categories with 
	 * the associated amount of articles */
	public function getCategoryList(){
		$result = array();
		$sql = "SELECT * FROM ".$this->db_name.".category_list "
			."WHERE site_id = ".mysqli_real_escape_string($this->link,$this->getSiteId())
			." AND language_code = '".mysqli_real_escape_string
						($this->link,$this->getLanguageCode())."'";
		$category_result = mysqli_query($this->link,$sql);
		echo mysqli_error($this->link);
		$i = 0;
		while ($row = mysqli_fetch_assoc($category_result)) {
			$result[$i] = array();
			$result[$i]["name"] = $row["name"];
			$result[$i]["url"] = $row["url"];
			$result[$i]["body"] = $row["body"];
			$result[$i]["article_count"] = $row["article_count"];
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;
		
		mysqli_free_result($category_result);
		return $result;
	}
	
	/* queries the database and returns whether the username passed 
	 * represents a user that has administrator priviliges and returns
	 * a bool value whether that is true or not */
	public function userIsAdmin($username){
		$result = false;
		$sql = "CALL ".$this->db_name.".user_is_admin('".$username."');";
		$is_admin_result = mysqli_query($this->link,$sql);
		while ($row = mysqli_fetch_assoc($is_admin_result)) {
			if($row["is_admin"]=="1"){
				$result = true;
			}
		}
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}if(!is_bool($is_admin_result)){
			mysqli_free_result($is_admin_result);
		}
		
		// increment the counter
		$this->procedureCallCount++;
		
		return $result;
	}
	
	/* adds or updates a category in the database */
	public function addCategory($name,$url,$body){
		$sql = "CALL ".$this->db_name.".add_category ('"
				.mysqli_real_escape_string($this->link,$name)."',"
				."'".mysqli_real_escape_string($this->link,$url)."', "
				."'".mysqli_real_escape_string($this->link,$body)."',"
				.mysqli_real_escape_string($this->link,$this->getSiteId()).","
				."'".mysqli_real_escape_string($this->link,$this->getLanguageCode())."')";

		$addcategory_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($addcategory_result)){
			mysqli_free_result($addcategory_result);
		}

		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* deletes a category from the database */
	public function deleteCategory($name){
		$sql = "CALL ".$this->db_name.".delete_category "
				."('".mysqli_real_escape_string($this->link,$name)."')";
		// fire the sp on the existing link
		$delete_category_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($delete_category_result)){
			mysqli_free_result($delete_category_result);
		}		
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* adds or updates a user in the database */
	public function addUser($name,$ext_id,$firstname,$lastname,$password,$is_admin){
		$is_admin_sql = "0";
		if($is_admin){ $is_admin_sql = "1"; }
		//build the sql for the sp
		$sql = "CALL ".$this->db_name.".add_user ('".$name."','".$ext_id."','".$firstname."',"
					."'".$lastname."','".$password."',".$is_admin_sql.");";
		
		// fire the sp on the existing link
		$adduser_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($adduser_result)){
			mysqli_free_result($adduser_result);
		}
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* adds an article to category link */
	public function addArticleCategory($article,$category){
		$sql = "CALL ".$this->db_name.".add_article_category ('"
			.mysqli_real_escape_string($this->link,$article)."','"
			.mysqli_real_escape_string($this->link,$category)."',"
			.mysqli_real_escape_string($this->link,$this->getSiteId()).", "
			."'".mysqli_real_escape_string($this->link,$this->getLanguageCode())."')";

		// fire the sp on the existing link
		$addartcat_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($addartcat_result)){
			mysqli_free_result($addartcat_result);
		}
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* deletes an article category */
	public function deleteArticleCategory($article,$category){
			$sql = "CALL ".$this->db_name.".delete_article_category ('"
			.mysqli_real_escape_string($this->link,$article)."','"
			.mysqli_real_escape_string($this->link,$category)."')";
			
		// fire the sp on the existing link
		$addartcat_result = mysqli_query($this->link,$sql);
		
		/* out of sync fix */
		if(mysqli_more_results($this->link)){
			mysqli_next_result($this->link);
		}
		if(!is_bool($addartcat_result)){
			mysqli_free_result($addartcat_result);
		}		
		
		// increment the counter
		$this->procedureCallCount++;
	}
	
	/* returns the username for a name or an empty string */
	public function getUserByName($firstname, $lastname){
		$result = "";
		$sql = "SELECT user_name FROM ".$this->db_name.".user "
				."WHERE user_firstname = '".$firstname."' "
				."AND user_lastname = '".$lastname."'";
		$user_result = mysqli_query($this->link,$sql);
		while($row = mysqli_fetch_assoc($user_result)){
			$result = $row["user_name"];
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($user_result);
		return $result;		
	}
	
	/* returns all article/category associations */
	public function getArticleCategoryList(){
		$result = array();
		$sql = "SELECT * FROM ".$this->db_name.".article_category_list "
				."WHERE ".$this->db_name.".article_category_list.site_id = "
				.mysqli_real_escape_string($this->link,$this->getSiteId())
				." ORDER BY ".$this->db_name.".article_category_list.article_date DESC";
		$artcat_result = mysqli_query($this->link,$sql);
		$i = 0;
		while($row = mysqli_fetch_assoc($artcat_result)){
			$result[$i] = $row;
			// add a timestamp to the result item
			$result[$i]["article_date_ts"] = strtotime($result[$i]["article_date"]);
			$i++;
		}
		
		// increment the counter
		$this->viewQueryCount++;

		mysqli_free_result($artcat_result);
		return $result;
	}
}
?>
